package yf.utils;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import yf.bean.EquivalenceTesting;
import yf.bean.TestCase;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class GetTestCase {
    private static TestCase testCase=new TestCase();

    private static List list=new ArrayList<EquivalenceTesting>();
    public static TestCase getTestCase(String filePath,String sheetName) throws IOException {
        EquivalenceTesting equivalenceTesting=null;
        InputStream inputStream =GetTestCase.class.getResourceAsStream(filePath);// 将excel模板文件转为输入流
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);// 创建excel模板workbook，
        //XSSFWorkbook workbook = new XSSFWorkbook(GetTestCase.class.getResourceAsStream(filePath));
        XSSFSheet sheet = workbook.getSheet(sheetName);
        int rows=sheet.getPhysicalNumberOfRows();
        testCase.setCaseName(getStringCellValue(sheet.getRow(0).getCell(1)));
        testCase.setPath(getStringCellValue(sheet.getRow(1).getCell(1)));
        testCase.setRequestType(getStringCellValue(sheet.getRow(2).getCell(1)));
        for(int i=4;i<rows;i++){
            if(getStringCellValue(sheet.getRow(i).getCell(3))=="N"||getStringCellValue(sheet.getRow(i).getCell(3))=="n"){
                continue;
            }else{
                equivalenceTesting=new EquivalenceTesting();
                equivalenceTesting.setTestCaseNo(getStringCellValue(sheet.getRow(i).getCell(0)));
                equivalenceTesting.setHeadMessage(getStringCellValue(sheet.getRow(i).getCell(1)));
                equivalenceTesting.setTestPoint(getStringCellValue(sheet.getRow(i).getCell(2)));
                equivalenceTesting.setParameter(getStringCellValue(sheet.getRow(i).getCell(4)));
                equivalenceTesting.setStatus(Integer.parseInt(getCellFormatValue(sheet.getRow(i).getCell(5))));
                equivalenceTesting.setResult(getStringCellValue(sheet.getRow(i).getCell(6)));
                equivalenceTesting.setSqlResult(getStringCellValue(sheet.getRow(i).getCell(7)));
                list.add(equivalenceTesting);
            }

        }
        testCase.setCases(list);
        workbook.close();
        return testCase;


    }
    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private static String  getStringCellValue(XSSFCell cell) {
        String strCell = "";
        switch (cell.getCellTypeEnum()) {
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell
     *            Excel单元格
     * @return String 单元格数据内容
     */
    private static String getDateCellValue(XSSFCell cell) {
        String result = "";
        switch (cell.getCellTypeEnum()) {
            case STRING:
                String date1 = getStringCellValue(cell);
                result = date1.replaceAll("[年月]", "-").replace("日", "").trim();
                break;
            case NUMERIC:
                Date date = new Date();
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                int year = cal.get(Calendar.YEAR);
                int month = cal.get(Calendar.MONTH) + 1;
                int day = cal.get(Calendar.DAY_OF_MONTH);
                result = year + "-" + month + "-" + day;
                break;
            case BLANK:
                result = "";
                break;

        }
        return result;
    }

    /**
     * 根据XSSFCell类型设置数据
     * @param cell
     * @return
     */
    private static String getCellFormatValue(XSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellTypeEnum()) {
                // 如果当前Cell的Type为NUMERIC
                case NUMERIC:
                    //去掉.0
                    cellvalue = Double.valueOf(cell.getNumericCellValue()).longValue()+"";
                    break;
                case FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式

                        //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                        //cellvalue = cell.getDateCellValue().toLocaleString();

                        //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);

                    }
                    // 如果是纯数字
                    else {
                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }

            return cellvalue;
    }
    }

